var express = require('express')
var router = express.Router()
// 导入自己封装的数据库操作模块
const exec = require('../db')
//导入调试的标记
const { debug } = require('../config')

// 添加一条交通事件记录
router.post('/event', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const {
    user_id,
    event_number,
    event_type,
    event_addr,
    event_location,
    event_datetime,
    event_describe,
    event_car
  } = req.body
  let sql = `insert into gg_event (
    user_id,
		event_number,
    event_type,
    event_addr,
    event_location,
    event_datetime,
    event_describe,
    event_car) values ('${user_id}','${event_number}','${event_type}','${event_addr}','${event_location}','${event_datetime}','${event_describe}','${event_car}')`

  try {
    let data = await exec(sql)
    res.send({
      code: 1,
      msg: '添加交通事件成功!',
      result: null
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '添加交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 分页查询所有未审核的交通事件(联合gg_user表查询)
router.get('/event/all/unchecked', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { pages, num } = req.query
  let sql_data = `select 
  event_id,
  event_number,
  event_type,
  event_addr,
  event_location,
  event_datetime,
  event_describe,
  event_car,
  user_truname,
  user_phone,
  event_check,
  event_status,
  event_level
   from gg_event right join gg_user on gg_event.user_id=gg_user.id where event_check="未审核" limit ${
     (pages - 1) * num
   },${num}`
  let sql_total = `select count(*) from gg_event where event_check="未审核"`
  try {
    let data = await exec(sql_data)
    let total = await exec(sql_total)
    res.send({
      code: 1,
      msg: '查询所有未审核的交通事件成功!',
      result: { total: total[0]['count(*)'], data: data }
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '查询所有未审核的交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 分页查询所有已审核的交通事件(联合gg_user表查询)
router.get('/event/all/checked', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { pages, num } = req.query
  let sql_data = `select 
  event_id,
  event_number,
  event_type,
  event_addr,
  event_location,
  event_datetime,
  event_describe,
  event_car,
  user_truname,
  user_phone,
  event_check,
  event_status,
  event_level
   from gg_event right join gg_user on gg_event.user_id=gg_user.id where event_check="已审核" limit ${
     (pages - 1) * num
   },${num}`
  let sql_total = `select count(*) from gg_event where event_check="已审核"`
  try {
    let data = await exec(sql_data)
    let total = await exec(sql_total)
    res.send({
      code: 1,
      msg: '查询所有已审核的交通事件成功!',
      result: { total: total[0]['count(*)'], data: data }
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '查询所有已审核的交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 分页查询指定字段并且已审核的交通事件(联合gg_user表查询)
router.get('/event/limit/checked', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { attr, value, pages, num } = req.query
  let sql_data = `select 
  event_id,
  event_number,
  event_type,
  event_addr,
  event_location,
  event_datetime,
  event_describe,
  event_car,
  user_truname,
  user_phone,
  event_check,
  event_status,
  event_level
   from gg_event right join gg_user on gg_event.user_id=gg_user.id where ${attr} like '%${value}%' and event_check="已审核" limit ${
    (pages - 1) * num
  },${num} `
  let sql_total = `select count(*) from gg_event where ${attr} like '%${value}%' and event_check="已审核"`
  try {
    let data = await exec(sql_data)
    let total = await exec(sql_total)
    res.send({
      code: 1,
      msg: '查询指定字段并且已审核的交通事件成功!',
      result: { total: total[0]['count(*)'], data: data }
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '查询指定字段并且已审核的交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 修改指定id的交通事件
router.put('/event', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { id } = req.query
  const attrArr = Object.keys(req.body)
  let sql = `update gg_event set \t` //set后面要有空格
  for (let i = 0; i < attrArr.length; i++) {
    if (i <= 0) {
      sql += `${attrArr[i]}='${req.body[attrArr[i]]}'`
    } else {
      sql += `,${attrArr[i]}='${req.body[attrArr[i]]}'`
    }
  }
  sql += `\t where event_id=${id}` //where前面要有空格
  try {
    let data = await exec(sql)
    res.send({
      code: 1,
      msg: '修改交通事件成功!',
      result: null
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '修改交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 查询每种处理状态的交通事件数
router.get('/event/status/num', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  let sql_0 = `select count(*) as num from gg_event where event_status='${'未处理'}' and event_check='已审核'`
  let sql_1 = `select count(*) as num from gg_event where event_status='${'处理中'}' and event_check='已审核'`
  let sql_2 = `select count(*) as num from gg_event where event_status='${'已处理'}' and event_check='已审核'`
  let sql_3 = `select count(*) as num from gg_event where event_status='${'已忽略'}' and event_check='已审核'`
  try {
    let data_0 = await exec(sql_0)
    let data_1 = await exec(sql_1)
    let data_2 = await exec(sql_2)
    let data_3 = await exec(sql_3)
    res.send({
      code: 1,
      msg: '查询指定字段并且已审核的交通事件成功!',
      result: {
        data: [
          { status: '未处理', num: data_0[0]['num'] },
          { status: '处理中', num: data_1[0]['num'] },
          { status: '已处理', num: data_2[0]['num'] },
          { status: '已忽略', num: data_3[0]['num'] }
        ]
      }
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '查询指定字段并且已审核的交通事件失败!',
      result: debug ? err : ''
    })
  }
})

// 查询指定年份每个月的交通事件数
router.get('/event/month/num', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { year } = req.query
  let sql_01 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=1 and YEAR(event_datetime)=${year}`
  let sql_02 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=2 and YEAR(event_datetime)=${year}`
  let sql_03 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=3 and YEAR(event_datetime)=${year}`
  let sql_04 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=4 and YEAR(event_datetime)=${year}`
  let sql_05 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=5 and YEAR(event_datetime)=${year}`
  let sql_06 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=6 and YEAR(event_datetime)=${year}`
  let sql_07 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=7 and YEAR(event_datetime)=${year}`
  let sql_08 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=8 and YEAR(event_datetime)=${year}`
  let sql_09 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=9 and YEAR(event_datetime)=${year}`
  let sql_10 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=10 and YEAR(event_datetime)=${year}`
  let sql_11 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=11 and YEAR(event_datetime)=${year}`
  let sql_12 = `select count(*) as num from gg_event where event_check='已审核' and MONTH(event_datetime)=12 and YEAR(event_datetime)=${year}`

  try {
    let data_01 = await exec(sql_01)
    let data_02 = await exec(sql_02)
    let data_03 = await exec(sql_03)
    let data_04 = await exec(sql_04)
    let data_05 = await exec(sql_05)
    let data_06 = await exec(sql_06)
    let data_07 = await exec(sql_07)
    let data_08 = await exec(sql_08)
    let data_09 = await exec(sql_09)
    let data_10 = await exec(sql_10)
    let data_11 = await exec(sql_11)
    let data_12 = await exec(sql_12)
    res.send({
      code: 1,
      msg: '查询指定年份每个月的交通事件数成功!',
      result: {
        data: [
          { month: '1', num: data_01[0]['num'] },
          { month: '2', num: data_02[0]['num'] },
          { month: '3', num: data_03[0]['num'] },
          { month: '4', num: data_04[0]['num'] },
          { month: '5', num: data_05[0]['num'] },
          { month: '6', num: data_06[0]['num'] },
          { month: '7', num: data_07[0]['num'] },
          { month: '8', num: data_08[0]['num'] },
          { month: '9', num: data_09[0]['num'] },
          { month: '10', num: data_10[0]['num'] },
          { month: '11', num: data_11[0]['num'] },
          { month: '12', num: data_12[0]['num'] }
        ]
      }
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '查询指定年份每个月的交通事件数失败!',
      result: debug ? err : ''
    })
  }
})

// 删除指定id的交通事件
router.delete('/event', async (req, res) => {
  /*
    let { id } = req.params   // 获取:后面的参数 /:id
    const { pages, num } = req.query   // 获取?后面的参数 /messages?pages=1&num=5
    let { username, password } = req.body   // 获取请求体参数 {}
  */
  const { id } = req.query
  const attrArr = Object.keys(req.body)
  let sql = `delete from gg_event where event_id=${id}`
  try {
    let data = await exec(sql)
    res.send({
      code: 1,
      msg: '删除交通事件成功!',
      result: null
    })
  } catch (err) {
    res.send({
      code: 0,
      msg: '删除交通事件失败!',
      result: debug ? err : ''
    })
  }
})

module.exports = router
